﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using Skyworth.CRM.Service.Entity;
using Skyworth.CRM.Service.IDAL;

namespace Skyworth.CRM.DAL
{
   public class PolicySchemeDAL:SuperDataBase,IPolicySchemeDAL
    {
       public void Add(PolicyScheme model)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("insert into T_Sales_PriceList");
           strSql.Append(" ( PolicyId,SchemeName,SchemeCode,Unit,Price,TaxRatio,TaxPrice,PriceType,ProductId,StartDate,EndDate,SpecialType)");
           strSql.Append(" values ( @PolicyId,@SchemeName,@SchemeCode,@Unit,@Price,@TaxRatio,@taxPrice,@PriceType,@productId,@startDate,@endDate,@specialType )");

           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@PolicyId", DbType.Int32, model.PolicyId);
           DB.AddInParameter(cmd, "@SchemeName", DbType.String, model.SchemeName);
           DB.AddInParameter(cmd, "@SchemeCode", DbType.String, model.SchemeCode);
           DB.AddInParameter(cmd, "@Unit", DbType.String, model.Unit);
           DB.AddInParameter(cmd, "@Price", DbType.Decimal, model.Price);
           DB.AddInParameter(cmd, "@TaxRatio",DbType.Decimal,model.TaxRatio);
           DB.AddInParameter(cmd, "@taxPrice",DbType.Decimal,model.TaxPrice);
           DB.AddInParameter(cmd, "@PriceType", DbType.String, model.PriceType);
           DB.AddInParameter(cmd, "@productId", DbType.Int32, model.ProductId);
           DB.AddInParameter(cmd, "@startDate", DbType.DateTime, model.StartTime);
           DB.AddInParameter(cmd, "@endDate", DbType.DateTime, model.EndTime);
           DB.AddInParameter(cmd, "@specialType", DbType.Boolean, model.IsSpecialType);
           DB.ExecuteNonQuery(cmd);
       }

       public void Update(PolicyScheme model)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("update T_Sales_PriceList set ");
           strSql.Append("PolicyId=@PolicyId,");
           strSql.Append("SchemeName=@SchemeName,");
           strSql.Append("SchemeCode=@SchemeCode,");
           strSql.Append("Unit=@Unit,");
           strSql.Append("Price=@Price,");
           strSql.Append("TaxPrice=@taxPrice,");
            strSql.Append("TaxRatio=@TaxRatio,"); 
           strSql.Append("PriceType=@PriceType,");
           strSql.Append("ProductId=@productId,");
           strSql.Append("StartDate=@startDate,");
           strSql.Append("EndDate=@endDate,");
           strSql.Append("SpecialType=@specialType");
           strSql.Append(" where Id=@Id");
           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@Id", DbType.Int32, model.Id);
           DB.AddInParameter(cmd, "@PolicyId", DbType.Int32, model.PolicyId);
           DB.AddInParameter(cmd, "@SchemeName", DbType.String, model.SchemeName);
           DB.AddInParameter(cmd, "@SchemeCode", DbType.String, model.SchemeCode);
           DB.AddInParameter(cmd, "@Unit", DbType.String, model.Unit);
           DB.AddInParameter(cmd, "@Price", DbType.Decimal, model.Price);
           DB.AddInParameter(cmd, "@TaxRatio", DbType.Decimal, model.TaxRatio);
           DB.AddInParameter(cmd, "@taxPrice",DbType.Decimal,model.TaxPrice);
           DB.AddInParameter(cmd, "@PriceType", DbType.String, model.PriceType);
           DB.AddInParameter(cmd, "@productId", DbType.Int32, model.ProductId);
           DB.AddInParameter(cmd, "@startDate", DbType.DateTime, model.StartTime);
           DB.AddInParameter(cmd, "@endDate", DbType.DateTime, model.EndTime);
           DB.AddInParameter(cmd, "@specialType", DbType.Boolean, model.IsSpecialType);
           DB.ExecuteNonQuery(cmd);
       }

       public DataTable Query(string strWhere, Hashtable parameter)
       {
           throw new NotImplementedException();
       }

       public void Delete(int id)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append(" delete T_Sales_PriceList where Id=@Id");
           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@Id", DbType.Int32, id);
           DB.ExecuteNonQuery(cmd);
       }

       public PolicyScheme GetPolicyById(int id)
       {
           PolicyScheme model = null;
           StringBuilder strSql = new StringBuilder();
           strSql.Append("select top 1 Id,policyId,SchemeName,SchemeCode,unit,price,TaxRatio,TaxPrice,PriceType,tsp.ProductId,StartDate,EndDate,SpecialType,tbp.ProductName from T_Sales_PriceList as tsp ");
           strSql.Append(" left join T_BasicData_Product as tbp on tsp.ProductId=tbp.ProductId ");
           strSql.Append(" where Id=@Id");
           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@Id", DbType.Int32, id);
           using (IDataReader dr = DB.ExecuteReader(cmd))
           {
               while (dr.Read())
               {
                   model = new PolicyScheme();
                   model.Id =id;
                   if (dr["PolicyId"].ToString() != "")
                   {
                       model.PolicyId = int.Parse(dr["PolicyId"].ToString());
                   }
                   if (dr["ProductId"].ToString() != "")
                   {
                       model.ProductId = int.Parse(dr["ProductId"].ToString());
                   }
                   model.SchemeName = dr["SchemeName"].ToString();
                   model.SchemeCode = dr["SchemeCode"].ToString();
                   model.Unit = dr["unit"].ToString();
                   model.Price =Decimal.Parse(dr["price"].ToString());
                   model.TaxRatio = decimal.Parse(dr["TaxRatio"].ToString());
                   model.TaxPrice = decimal.Parse(dr["TaxPrice"].ToString());
                   model.PriceType = dr["PriceType"].ToString();

                   model.StartTime =DateTime.Parse(dr["StartDate"].ToString());
                   model.EndTime = DateTime.Parse(dr["EndDate"].ToString());
                   model.IsSpecialType =Convert.ToBoolean(dr["SpecialType"].ToString());
                   model.ProductName = dr["ProductName"].ToString();
               }
           }
           return model;
       }

       public DataTable GetPoliciesByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
       {
           recordCount = 0;
           pageCount = 0;
           StringBuilder strCommandText = new StringBuilder();
           DataSet ds;
           strCommandText.AppendFormat(
               "SELECT row_number() over({0}) AS rownum,Id, tsp.PolicyId, SchemeName, SchemeCode,Unit,Price,TaxRatio,TaxPrice,tbp.ProductName,convert(varchar(10),StartDate,121) StartDate,convert(varchar(10),EndDate,121) EndDate,", strOrderBy);
           strCommandText.Append(" case SpecialType when 1 then '是' else '否' end as SpecialType,case PriceType when 1 then '离岸价' when 2 then '到岸价' else '未知' end as PriceType,tbp.ProductId,p.PolicyName from T_Sales_PriceList as tsp ");
           strCommandText.Append(" left join T_BasicData_Product as tbp on tsp.ProductId=tbp.ProductId ");
           strCommandText.Append(" inner join T_Sales_PricePolicy p on tsp.PolicyId=p.PolicyId  ");
           strCommandText.Append(strWhere);

           System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
           DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
           DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
           DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
           DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
           DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
           DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
           DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
           ds = DB.ExecuteDataSet(sqlcommand);
           recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
           pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));

           return ds.Tables[0];
       }

       public DataTable QueryPolicyScheme(string strWhere)
       {
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append("SELECT Id, PolicyId, SchemeName, SchemeCode,ProductUnit,Unit,Price,TaxRatio,TaxPrice,tbp.ProductName,convert(varchar(10),StartDate,121) StartDate,convert(varchar(10),EndDate,121)  EndDate,SpecialType,PriceType,");
               strSql.Append(" case SpecialType when 1 then '是' else '否' end as SpecialTypeName,case PriceType when 1 then '离岸价' when 2 then '到岸价' else '未知' end as PriceTypeName,tbp.ProductId from T_Sales_PriceList as tsp ");
               strSql.Append(" left join T_BasicData_Product as tbp on tsp.ProductId=tbp.ProductId where 1=1 ");
            
               

               if (strWhere.Trim() != "")
               {
                   strSql.Append(strWhere);
               }
               DataSet ds = DB.ExecuteDataSet(CommandType.Text, strSql.ToString());
               return ds.Tables[0];
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }

    }
}
